﻿unit ExcelToPdfConverter;

interface

uses
  SysUtils, ComObj, System.IOUtils, Vcl.Dialogs, Variants, Vcl.StdCtrls;

type
  TExcelToPdfConverter = class
  public
    // 批量转换主函数（支持递归遍历子目录）
    class procedure BatchConvert(const InputFolder, OutputFolder: string;
              PreserveStructure: Boolean; LogMemo: TMemo = nil);
  private
    class function TryConvertWithExcel(const InputFile, OutputFile: string; out ErrorMsg: string): Boolean;
    class function TryConvertWithWPS(const InputFile, OutputFile: string; out ErrorMsg: string): Boolean;
    class function GetRelativePath(const BasePath, TargetPath: string): string;
    class function IsExcelFile(const FileName: string): Boolean;
    class function GetUniqueFileName(const BaseFolder, FileName: string): string;
    class procedure Log(const Msg: string; LogMemo: TMemo);
  end;

implementation

{ TExcelToPdfConverter }

// 批量转换主函数（核心优化：递归获取所有子目录文件）
class procedure TExcelToPdfConverter.BatchConvert(const InputFolder, OutputFolder: string;
          PreserveStructure: Boolean; LogMemo: TMemo);
var
  FileList: TArray<string>;  // 存储所有文件（包括子目录）
  InputFileName, OutputFileName, FileNameWithoutExt, TargetFolder, RelativePath: string;
  i: Integer;
  ConvertSuccess: Boolean;
  TotalFiles, ProcessedFiles, SuccessFiles: Integer;
  ErrorMsg: string;
begin
  TotalFiles := 0;
  ProcessedFiles := 0;
  SuccessFiles := 0;
  Log('开始转换任务（递归遍历所有子目录）...', LogMemo);
  Log('输入根目录: ' + InputFolder, LogMemo);
  Log('输出根目录: ' + OutputFolder, LogMemo);

  if not TDirectory.Exists(InputFolder) then
  begin
    ShowMessage('输入文件夹不存在: ' + InputFolder);
    Log('错误：输入文件夹不存在', LogMemo);
    Exit;
  end;

  // 创建输出根目录（如果不存在）
  if not TDirectory.Exists(OutputFolder) then
  begin
    try
      TDirectory.CreateDirectory(OutputFolder);
      Log('已创建输出根目录: ' + OutputFolder, LogMemo);
    except
      on E: Exception do
      begin
        ShowMessage('无法创建输出文件夹: ' + E.Message);
        Log('错误：创建输出文件夹失败 - ' + E.Message, LogMemo);
        Exit;
      end;
    end;
  end;

  // 核心优化：递归获取所有子目录的文件（TSearchOption.soAllDirectories）
  FileList := TDirectory.GetFiles(InputFolder, '*.*', TSearchOption.soAllDirectories);
  TotalFiles := Length(FileList);
  Log(Format('共发现 %d 个文件（含子目录），开始筛选Excel文件...', [TotalFiles]), LogMemo);

  if TotalFiles = 0 then
  begin
    ShowMessage('输入目录及子目录中未找到任何文件');
    Log('警告：输入目录及子目录为空', LogMemo);
    Exit;
  end;

  // 遍历所有文件（包括子目录中的文件）
  for i := 0 to High(FileList) do
  begin
    InputFileName := FileList[i];
    Log(Format('处理第 %d 个文件: %s', [i+1, InputFileName]), LogMemo);

    // 检查是否为Excel文件
    if not IsExcelFile(InputFileName) then
    begin
      Log('  跳过：非Excel文件', LogMemo);
      Continue;
    end;

    ProcessedFiles := ProcessedFiles + 1;

    // 生成输出路径（保持子目录结构时，计算相对路径）
    FileNameWithoutExt := ChangeFileExt(ExtractFileName(InputFileName), '');
    if PreserveStructure then
    begin
      // 计算当前文件所在目录相对于输入根目录的相对路径（关键：保持子目录结构）
      RelativePath := GetRelativePath(InputFolder, ExtractFilePath(InputFileName));
      TargetFolder := TPath.Combine(OutputFolder, RelativePath);
      // 创建对应的子目录（如果不存在）
      if not TDirectory.Exists(TargetFolder) then
      begin
        TDirectory.CreateDirectory(TargetFolder);
        Log('  创建子目录: ' + TargetFolder, LogMemo);
      end;
    end
    else
    begin
      // 不保持结构时，所有文件都输出到根目录
      TargetFolder := OutputFolder;
    end;

    // 生成唯一的PDF文件名
    OutputFileName := GetUniqueFileName(TargetFolder, FileNameWithoutExt + '.pdf');
    Log('  目标PDF路径: ' + OutputFileName, LogMemo);

    // 尝试转换（先Excel后WPS）
    ConvertSuccess := False;
    ErrorMsg := '';

    // 先尝试微软Excel
    ConvertSuccess := TryConvertWithExcel(InputFileName, OutputFileName, ErrorMsg);
    if ConvertSuccess then
      Log('  转换成功（Excel）', LogMemo)
    else
    begin
      Log('  Excel转换失败: ' + ErrorMsg, LogMemo);
      // Excel失败，尝试WPS
      ConvertSuccess := TryConvertWithWPS(InputFileName, OutputFileName, ErrorMsg);
      if ConvertSuccess then
        Log('  转换成功（WPS）', LogMemo)
      else
        Log('  WPS转换失败: ' + ErrorMsg, LogMemo);
    end;

    // 校验文件是否实际生成
    if ConvertSuccess and not TFile.Exists(OutputFileName) then
    begin
      ConvertSuccess := False;
      Log('  警告：转换返回成功，但文件未生成！', LogMemo);
    end;

    if ConvertSuccess then
      SuccessFiles := SuccessFiles + 1;
  end;

  // 输出统计结果
  Log('', LogMemo);
  Log(Format('转换任务结束 | 总文件数: %d | 有效Excel文件: %d | 成功转换: %d',
    [TotalFiles, ProcessedFiles, SuccessFiles]), LogMemo);

  ShowMessage(Format(
    '转换任务已完成！'#13#10 +
    '总文件数（含子目录）: %d 个'#13#10 +
    '有效Excel文件: %d 个'#13#10 +
    '成功转换为PDF: %d 个',
    [TotalFiles, ProcessedFiles, SuccessFiles]
  ));
end;

// 尝试用微软Excel转换
class function TExcelToPdfConverter.TryConvertWithExcel(const InputFile, OutputFile: string; out ErrorMsg: string): Boolean;
const
  xlTypePDF = 0;
var
  ExcelApp, Workbook: OleVariant;
begin
  Result := False;
  ErrorMsg := '';
  try
    try
      ExcelApp := CreateOleObject('Excel.Application');
    except
      on E: Exception do
      begin
        ErrorMsg := '创建Excel对象失败: ' + E.Message;
        Exit;
      end;
    end;

    try
      ExcelApp.Visible := False;
      ExcelApp.DisplayAlerts := False;

      try
        Workbook := ExcelApp.Workbooks.Open(InputFile);
      except
        on E: Exception do
        begin
          ErrorMsg := '打开文件失败: ' + E.Message;
          Exit;
        end;
      end;

      try
        Workbook.ExportAsFixedFormat(xlTypePDF, OutputFile);
        // 检查文件是否生成
        if TFile.Exists(OutputFile) then
          Result := True
        else
          ErrorMsg := '导出PDF成功，但文件未找到';
      except
        on E: Exception do
          ErrorMsg := '导出PDF失败: ' + E.Message;
      end;
    finally
      if not VarIsEmpty(Workbook) then
      begin
        Workbook.Close(SaveChanges := False);
        Workbook := Unassigned;
      end;
      if not VarIsEmpty(ExcelApp) then
      begin
        ExcelApp.Quit;
        ExcelApp := Unassigned;
      end;
    end;
  except
    on E: Exception do
      ErrorMsg := 'Excel处理异常: ' + E.Message;
  end;
end;

// 尝试用WPS转换
class function TExcelToPdfConverter.TryConvertWithWPS(const InputFile, OutputFile: string; out ErrorMsg: string): Boolean;
const
  WpsTypePDF = 44; // 多数WPS版本适用，若失败可尝试32或0
var
  WpsApp, Workbook: OleVariant;
begin
  Result := False;
  ErrorMsg := '';
  try
    try
      WpsApp := CreateOleObject('KET.Application');
    except
      on E: Exception do
      begin
        ErrorMsg := '创建WPS对象失败: ' + E.Message;
        Exit;
      end;
    end;

    try
      WpsApp.Visible := False;
      WpsApp.DisplayAlerts := False;

      try
        Workbook := WpsApp.Workbooks.Open(InputFile);
      except
        on E: Exception do
        begin
          ErrorMsg := '打开文件失败: ' + E.Message;
          Exit;
        end;
      end;

      try
        Workbook.ExportAsFixedFormat(WpsTypePDF, OutputFile);
        // 检查文件是否生成
        if TFile.Exists(OutputFile) then
          Result := True
        else
          ErrorMsg := '导出PDF成功，但文件未找到';
      except
        on E: Exception do
          ErrorMsg := '导出PDF失败: ' + E.Message;
      end;
    finally
      if not VarIsEmpty(Workbook) then
      begin
        Workbook.Close(SaveChanges := False);
        Workbook := Unassigned;
      end;
      if not VarIsEmpty(WpsApp) then
      begin
        WpsApp.Quit;
        WpsApp := Unassigned;
      end;
    end;
  except
    on E: Exception do
      ErrorMsg := 'WPS处理异常: ' + E.Message;
  end;
end;

// 日志输出辅助函数
class procedure TExcelToPdfConverter.Log(const Msg: string; LogMemo: TMemo);
begin
  if Assigned(LogMemo) then
  begin
    LogMemo.Lines.Add(FormatDateTime('[yyyy-mm-dd hh:nn:ss] ', Now) + Msg);
    LogMemo.ScrollBy(0, 1000); // 自动滚动到底部
  end;
end;

// 获取相对路径（关键：保持子目录结构时计算正确的嵌套关系）
class function TExcelToPdfConverter.GetRelativePath(const BasePath, TargetPath: string): string;
var
  BasePathAbs, TargetPathAbs: string;
  BaseLen, TargetLen, I: Integer;
begin
  // 转换为绝对路径并统一大小写，确保比较准确
  BasePathAbs := IncludeTrailingPathDelimiter(UpperCase(ExpandFileName(BasePath)));
  TargetPathAbs := IncludeTrailingPathDelimiter(UpperCase(ExpandFileName(TargetPath)));

  BaseLen := Length(BasePathAbs);
  TargetLen := Length(TargetPathAbs);

  // 找到两个路径的公共前缀结束位置
  I := 1;
  while (I <= BaseLen) and (I <= TargetLen) and (BasePathAbs[I] = TargetPathAbs[I]) do
    Inc(I);

  // 如果路径完全相同，返回空（表示当前目录）
  if (I > BaseLen) and (I > TargetLen) then
  begin
    Result := '';
    Exit;
  end;

  // 生成相对路径：先回退到公共目录
  Result := '';
  while I <= BaseLen do
  begin
    if BasePathAbs[I] = '\' then
      Result := Result + '..\'; // 每多一层目录，增加一个"../"
    Inc(I);
  end;

  // 拼接目标路径的剩余部分（子目录结构）
  Result := Result + Copy(TargetPathAbs, I, TargetLen);
  Result := ExcludeTrailingPathDelimiter(Result);
end;

// 检查是否为Excel文件（支持常见格式）
class function TExcelToPdfConverter.IsExcelFile(const FileName: string): Boolean;
var
  Ext: string;
begin
  Ext := UpperCase(ExtractFileExt(FileName));
  Result := (Ext = '.XLS') or (Ext = '.XLSX') or (Ext = '.XLSM') or
            (Ext = '.XLT') or (Ext = '.XLTX') or (Ext = '.XLTM') or
            (Ext = '.XLSB') or (Ext = '.XLA') or (Ext = '.XLAM'); // 扩展更多格式
end;

// 生成唯一文件名（避免子目录中同名文件冲突）
class function TExcelToPdfConverter.GetUniqueFileName(const BaseFolder, FileName: string): string;
var
  FullPath, Ext, NameOnly: string;
  Counter: Integer;
begin
  FullPath := TPath.Combine(BaseFolder, FileName);
  if not TFile.Exists(FullPath) then
  begin
    Result := FullPath;
    Exit;
  end;

  // 若文件已存在，添加计数器（如file.pdf → file_1.pdf）
  Ext := ExtractFileExt(FileName);
  NameOnly := ChangeFileExt(FileName, '');
  Counter := 1;
  repeat
    Result := TPath.Combine(BaseFolder, Format('%s_%d%s', [NameOnly, Counter, Ext]));
    Inc(Counter);
  until not TFile.Exists(Result);
end;

end.
